﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using AccessCommon;
using BackboneTest.Models;

namespace BackboneTest.DAL
{
    public partial class TB_UserDAL
    {
       #region 成员方法


        /// <summary>
        /// 判断是否存在该登录账号
        /// </summary>
        /// <param name="loginID"></param>
        /// <returns></returns>
        public bool Exists(string loginID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) from TB_User");
            strSql.Append(" where LoginID=@LoginID ");
            SqlParameter[] parameters = {
					new SqlParameter("@LoginID", SqlDbType.NVarChar,50)
                    };
            parameters[0].Value = loginID;

            object obj = mydb.ExecuteScalar(CommandType.Text, strSql.ToString(), parameters);
            if (obj == null || Convert.ToInt32(obj) < 1)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public TB_User GetModel(string loginID, string pwd)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ID,UserName,LoginID,PWD,Company,Email from TB_User ");
            strSql.Append(" where LoginID=@LoginID and PWD=@PWD ");
            SqlParameter[] parameters = {
					new SqlParameter("@LoginID", SqlDbType.NVarChar,50),
                    new SqlParameter("@PWD", SqlDbType.NVarChar,50)};
            parameters[0].Value = loginID;
            parameters[1].Value = pwd;

            TB_User model = new TB_User();
            DataSet ds = mydb.ExecuteDataset(CommandType.Text, strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                model.UserName = ds.Tables[0].Rows[0]["UserName"].ToString();
                model.LoginID = ds.Tables[0].Rows[0]["LoginID"].ToString();
                model.PWD = ds.Tables[0].Rows[0]["PWD"].ToString();
                model.Company = ds.Tables[0].Rows[0]["Company"].ToString();
                model.Email = ds.Tables[0].Rows[0]["Email"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }
    
       /// <summary>
       /// 分页列表
       /// </summary>
       /// <param name="TB_UserItem"></param>
       /// <param name="pageSize"></param>
       /// <param name="index"></param>
       /// <param name="Total"></param>
       /// <returns></returns>
       public DataTable GetList(TB_UserItem searchItem, int pageSize, int index, out int Total)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append("select ID,UserName,LoginID,PWD,Company,Email from TB_User   where 1=1  ");
        
           string order = " ID ";
           return mydb.GetPrintTable(strSql.ToString(), order, "*", pageSize, index, out Total);
        }
        /// <summary>
        /// 导入Excel数据
        /// </summary>
        /// <param name="dtExcel"></param>
        /// <returns></returns>
        public bool ExcelToDataBase(DataTable dtExcel)
        {
            mydb.BeginTransaction();
            try
            {
                dtExcel.Rows.RemoveAt(0);
                int colcount = dtExcel.Columns.Count;
                int rowcount = dtExcel.Rows.Count;
            
            
                foreach (DataRow dr in dtExcel.Rows)
                {
                    if (dr[0].ToString() != "")
                    {
                        TB_User model = new TB_User();
        
                        model.UserName = dr["UserName"].ToString();
                        model.LoginID = dr["LoginID"].ToString();
                        model.PWD = dr["PWD"].ToString();
                        model.Company = dr["Company"].ToString();
                        model.Email = dr["Email"].ToString();
                         if (Add(model)==-1)   //插入新记录
                        {
                            mydb.Rollback();
                            return false;
                        }
            
                    }
                }
                mydb.Commit();
                return true;
            
            }
            catch (Exception ex)
            {
                string strex = ex.ToString();
                mydb.Rollback();
                return false;
            }
            finally
            {
                mydb.EndTransaction();
            
            }
        }
    
        #endregion  成员方法
    
    }  
    
}

